﻿using INTEX.DataBase;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using WPFExcelReport.Helpers;
namespace WPFExcelReport
{
 
    struct vvVKPOS
    {
        public string pos;
        public double vkpqty;
        public double allocated;
        public int rolls;
        public double difference;
    }
    class JLTVR04DOCReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        public List<vvVKPOS> listVKPOS = new List<vvVKPOS>();
        SETINTEXDB INTEXDB = new SETINTEXDB();
        INTEXDBC INTEXDBC;
        string str1 = "", str2 = "", str3 = "";
        #endregion
        public JLTVR04DOCReport()
            : base()
        {

        }
        public JLTVR04DOCReport(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
            OracleDB = (OracleConnection)dicParamater["OracleDB"];
            proxy = (IntexReptServBLClient)dicParamater["proxy"];
            if (dicParamater.ContainsKey("ROOTDRIVE"))
            {
                Root = dicParamater["ROOTDRIVE"].ToString();
            }
            else
            {
                Root = "C:\\IntexReport";
            }
       
            INTEXDBC = new INTEXDBC(OracleDB, proxy);
        }
        private void FillInWBBST(DataRow dwData)
        {

            Dictionary<string, string> makeup = new Dictionary<string, string>();
            Dictionary<string, string> packup = new Dictionary<string, string>();
            DataRow dwVKPOS = null;
            if (!dsSource.Tables.Contains("WBBST"))
                return;
            if (!dsSource.Tables.Contains("VKPOS"))
                return;
            string VV = dwData["VRPLIEFSCH"].ToString();
            string KRS = dwData["VRPAUFKRS"].ToString();
            string LFD = dwData["VRPAUFLFD"].ToString();
            string POS = dwData["VRPAUFPOS"].ToString(); 
            string UM = "Y";
            if (dwData["VRPMEEH"].ToString().Contains("M"))
                UM = "M";
                  
            DataTable tbWB = dsSource.Tables["WBBST"];
            DataTable tbVKP = dsSource.Tables["VKPOS"];
            string sheet = KRS + "-" + Helper.GetSubRollNum(LFD) + "-" + Helper.GetSubRollNum(POS);
            string fileters = "WBBAUFKRS='" + KRS + "' and WBBAUFLFD='" + LFD + "' and WBBAUFPOS='" + POS + "' AND WBBALIEFSCH='" + VV + "'";
            DataRow[] frolls = tbWB.Select(fileters);
            if (frolls.Length == 0)
                return;
            var qvp = from DataRow dwTmp in tbVKP.AsEnumerable()
                      where dwTmp["VKPAUFKRS"].ToString() == KRS
                      && dwTmp["VKPAUFLFD"].ToString() == LFD
                      && dwTmp["VKPAUFPOS"].ToString() == POS
                      select dwTmp;
            foreach (DataRow dwTmp in qvp)
            {
                dwVKPOS = dwTmp;
            }
            if (dwVKPOS == null)
                return; 
            int rls = 1;
            exAgent.SetCurrentSheet("VKWZ");
            exAgent.CopySheet(sheet);
            exAgent.SetCurrentSheet(sheet);
            int maxlines = 44;
            int x = 1;
            int y = 1;
            int pages = 0;
            int turns = 0;
            int rolls = 0;
            int reserved = 12;
            int TRolls = 0;
            double vkptotal = 0;

         
            exAgent.SetCellValue(1, 24, VV);
            Helper.GetSplitDate(ref str1, ref str2, ref str3, date);
            exAgent.SetCellValue(1, 30, str3);
      
            string src = dwVKPOS["VKPKDARTNR"].ToString().TrimEnd() + "/" + dwVKPOS["VKPKDFARBNR"].ToString().TrimEnd() + "/" + dwVKPOS["VKPKDBEZ"].ToString().TrimEnd();
            src = GetGB2312Value(src);

            exAgent.SetCellValue(3, 21, "客人品种:" + src);
            src = dwVKPOS["VKPART"].ToString().TrimEnd() + "-" + dwVKPOS["VKPFARBE"].ToString();
            exAgent.SetCellValue(2, 21, "品       种:" + src);

            foreach (DataRow rwb in frolls)
            {
                //pages =0,rolls=0,turns=0
                //pages=0,rolls=37
                if (rolls > maxlines)
                {
                    rolls = 0;
                    turns++;
                    if (turns > 2)
                    {
                        turns = 0;
                        pages++;
                    }
                }
                if (pages > 0)
                {
                    reserved = 2;
                    maxlines = 54;
                }
                x = pages * 60 + reserved + rolls;
                y = turns * 11 + 1;

                string stk = rwb["WBBSTKNR"].ToString();
                double stkweight = 0;
                double stkgrossw = 0;
                INTEXDBC.GetStkWeight(rwb, ref  stkgrossw, ref  stkweight, ref makeup, ref  packup);

                double STKLG = System.Math.Round(System.Convert.ToDouble(rwb["WBBISTKLG"].ToString()) * 0.001, 1);
                double YLength = System.Math.Round(STKLG / 0.9144, 1);
                stk = Helper.GetSubRollNum(stk);
                if (UM == "Y")
                    STKLG = YLength;
                exAgent.SetCellValue(x, y, stk);
                exAgent.SetCellValue(x, y + 2, STKLG);
                exAgent.SetCellValue(x, y + 6, stkweight);
                exAgent.SetCellValue(x, y + 8, stkgrossw);
                rolls++;
                TRolls++;
                EventDispatcher.Instance.RaiseStatus("共" + rls + " 第" + TRolls, StatusEventType.RunningProgress);
                vkptotal += STKLG;
            }
            exAgent.SetCellValue(4, 21, "总长度:" + System.Math.Round(vkptotal, 0) + UM + ";总卷数" + TRolls);
            vvVKPOS vkp = new vvVKPOS();
            vkp.pos = POS;
            vkp.allocated = vkptotal;
            vkp.rolls = TRolls;
            vkp.vkpqty = 0;
            vkp.difference = vkp.vkpqty - vkptotal;
            listVKPOS.Add(vkp);
        }
        private void FillInEXCEL(int colums)
        {
            string src = "",sgb="";
            string sdkey = "";
            try
            {
                if (!dsSource.Tables.Contains("VKKOPF"))
                    return;
                if (!dsSource.Tables.Contains("VRKOPF"))
                    return;
                if (!dsSource.Tables.Contains("VRPOS"))
                    return;
                if (!dsSource.Tables.Contains("VKPOS"))
                    return;
                DataTable tbVKK = dsSource.Tables["VKKOPF"];
                DataTable tbVRK = dsSource.Tables["VRKOPF"];
                DataTable tbVRP= dsSource.Tables["VRPOS"];
                DataTable tbVKP = dsSource.Tables["VKPOS"];
                DataRow dwKDART = null;
                int rls = tbVRP.Rows.Count;
                if (tbVRK.Rows.Count == 0)
                    return;
                if (tbVRP.Rows.Count == 0)
                    return;
                DataRow dwVKKOPF = tbVKK.Rows[0];
                DataRow dwVRKOPF = tbVRK.Rows[0];
                DataRow dwVRP=tbVRP.Rows[0];
                string sheet = Helper.GetSubRollNum(dwVRP["VRPRENR"].ToString());
                exAgent.SetCurrentSheet("VR04DOC");
                exAgent.CopySheet(sheet);
                exAgent.SetCurrentSheet(sheet);

                exAgent.SetCellValue(23, 13, dwVKKOPF["VKKAUFKRS"].ToString() + "-" + dwVKKOPF["VKKAUFLFD"].ToString());
                Helper.GetSplitDate(ref str1, ref str2, ref str3, dwVKKOPF["VKKNEUDAT"].ToString());
                
                exAgent.SetCellValue(23, 27, str3);

                exAgent.SetCellValue(24, 13, dwVKKOPF["VKKBESTNR"].ToString());
                Helper.GetSplitDate(ref str1, ref str2, ref str3, dwVKKOPF["VKKBESTDAT"].ToString());
                exAgent.SetCellValue(24, 27, str3);

                int x=1,y=1;
                //VRKNAME1	VRKNAME2	VRKSTRASSE1
                x = 5; y = 1;
                exAgent.SetCellValue(x, y, Helper.GetGB2312Value(dwVRKOPF["VRKNAME1"].ToString()));
                x = 6; y = 1;
                exAgent.SetCellValue(x, y, Helper.GetGB2312Value(dwVRKOPF["VRKNAME2"].ToString()));
                x = 7; y = 1;
                exAgent.SetCellValue(x, y, Helper.GetGB2312Value(dwVRKOPF["VRKSTRASSE1"].ToString()));
                x = 12; y = 31;
                Helper.GetSplitDate(ref str1, ref str2, ref str3, date);
                exAgent.SetCellValue(x, y, str3);
                x = 11; y = 31;
                exAgent.SetCellValue(x, y, Helper.GetSubRollNum(dwVRKOPF["VRKKUNDNR"].ToString()));
                x = 21; y = 8;
                exAgent.SetCellValue(x, y, Helper.GetSubRollNum(dwVRKOPF["VRKKUNDNR"].ToString()));
                x = 21; y = 25;
                exAgent.SetCellValue(x,y,dwVRKOPF["VRKRENR"].ToString());
                src = Helper.GetHRName(dsSource, dwVRKOPF["VRKSACHBE"].ToString());
                exAgent.SetCellValue(5, 31, src);
                string currency = dwVRKOPF["VRKWAEHRSL"].ToString();
                x = 309; y = 33;
                exAgent.SetCellValue(x, y, Divide(dwVRKOPF["VRKWAWERT"].ToString(), 100));
                x = 311; y = 33;
                exAgent.SetCellValue(x, y, Divide(dwVRKOPF["VRKZUAB"].ToString(), 100));
                x = 313; y = 33;
                exAgent.SetCellValue(x, y, Divide(dwVRKOPF["VRKGESWERTB"].ToString(),100));

                src = dwVRKOPF["VRKZAHLBED"].ToString();
                var qz = from dwTemp in dsSource.Tables["TXTKON"].AsEnumerable()
                         where dwTemp["TXTIPIND"].ToString().Trim() == src.Trim() + "E"
                         select dwTemp;
                foreach (DataRow row in qz)
                {
                    exAgent.SetCellValue(309, 13, Helper.GetGB2312Value(row["TXT010"].ToString()));
                } 
                //exAgent.SetCellValue(106, 1, "发货条件/Delivery Terms:");
                src = Helper.GetDelTerm(dsSource, dwVKKOPF["VKKLIEFBED"].ToString());
                exAgent.SetCellValue(310, 13, src);
                //exAgent.SetCellValue(940, 46, prdg);

                exAgent.SetCellValue(40, 36, "Value" +Environment.NewLine+ "金额"+"("+currency+")");
                exAgent.SetCellValue(40, 28, "Price" + Environment.NewLine + "单价" + "(" + currency + "/" + dwVRP["VRPMEEH"].ToString().Trim()+")");
                //VRPRABART1	VRPRABPROZ1	VRPRABWERT1
                //VRPZUART1	VRPZUABPROZ1	VRPZUABWERT1
                int posx=41;
                int rpl=11;
                int rown=0;
                int count=tbVKP.Rows.Count;
                
                foreach (DataRow dwData in tbVRP.Rows)
                {
                    rown++;
                    //VVTLIEFSCH VVTAUFKRS	VVTAUFLFD	VVTAUFPOS
                    DataRow dwVKPOS = null;
                    string VV = dwData["VRPLIEFSCH"].ToString();
                    string KRS = dwData["VRPAUFKRS"].ToString();
                    string LFD = dwData["VRPAUFLFD"].ToString();
                    string POS = dwData["VRPAUFPOS"].ToString(); 
                
                    var qvp = from DataRow dwTmp in tbVKP.AsEnumerable()
                              where dwTmp["VKPAUFKRS"].ToString() == KRS
                              && dwTmp["VKPAUFLFD"].ToString() == LFD
                              && dwTmp["VKPAUFPOS"].ToString() == POS
                              select dwTmp;
                    foreach (DataRow dwTmp in qvp)
                    {
                        dwVKPOS = dwTmp;
                    }
                    if (dwVKPOS == null)
                        continue;
                    exAgent.SetCellValue(posx, 1,Helper.GetSubRollNum(POS));
                    //src = dwVKPOS["VKPART"].ToString().TrimEnd() + "-" + dwVKPOS["VKPFARBE"].ToString();
                    exAgent.SetCellValue(posx, 3, dwVKPOS["VKPART"].ToString());
                    exAgent.SetCellValue(posx, 8, dwVKPOS["VKPFARBE"].ToString());
                    exAgent.SetCellValue(posx, 13,Helper.GetSubRollNum(VV));
                    var qvv = from DataRow rvv in dsSource.Tables["VVLIEF"].AsEnumerable()
                              where rvv["VVLIEFNR"].ToString() == VV
                              select rvv;
                    foreach(DataRow rvv in qvv)
                    {
                        Helper.GetSplitDate(ref str1,ref str2,ref str3,rvv["VVLIEFDAT"].ToString());
                        exAgent.SetCellValue(posx, 19, str3);
                    }
                    #region================客人花号/品名======================
                    src = dwVKPOS["VKPKDARTNR"].ToString().TrimEnd() + " " + dwVKPOS["VKPKDBEZ"].ToString().TrimEnd() + " " + dwVKPOS["VKPKDFARBNR"].ToString().TrimEnd();
                    var qvakd = from DataRow row in dsSource.Tables["VAKDBEZ"].AsEnumerable()
                                where row["VAKBKOLL"].ToString() == dwVKPOS["VKPKOLL"].ToString()
                                && row["VAKBART"].ToString() == dwVKPOS["VKPART"].ToString()
                                && row["VAKBFARBE"].ToString() == dwVKPOS["VKPFARBE"].ToString()
                                select row;
                    foreach (DataRow rkd in qvakd)
                    {
                        src = rkd["VAKBKDARTNR"].ToString().TrimEnd() + " " + rkd["VAKBKDARTBEZ"].ToString().TrimEnd() + " " + rkd["VAKBKDFARBNR"].ToString().TrimEnd();
                        dwKDART = rkd;
                    }
                    exAgent.SetCellValue(posx + 1, 3, GetGB2312Value(src));
                    #endregion
                    #region================数量===================
                    src = dwData["VRPPRMENGE"].ToString();
                    exAgent.SetCellValue(posx, 26, Divide(src, 100));
                    src = dwVKPOS["VKPMENGE1"].ToString();
                    string VKPMENGE1 = Divide(src, 100);
                    exAgent.SetCellValue(posx+1, 28, "订单数量/Order Amount:"+VKPMENGE1);
                    exAgent.SetCellValue(posx, 30, dwData["VRPMEEH"].ToString().Trim());
                    src = dwData["VRPPREIS"].ToString();
                    exAgent.SetCellValue(posx, 33, Divide(src, 1000));
                    src = dwData["VRPWAWERT"].ToString();
                    exAgent.SetCellValue(posx, 36, Divide(src, 1000));
                    #endregion 
                    //VRPKOLL	VRPART	VRPFARBE
                    //VRPRABART1	VRPRABPROZ1	VRPRABWERT1//折扣
                    //VRPZUART1	VRPZUABPROZ1	VRPZUABWERT1//加价
                    string term = "";
                    decimal rate = 0;
                    decimal wert = 0;
                    int d = 0;
                    int a = 0;
                    
                    for (int j = 1; j < 5; j++)
                    {
                        term = dwData["VRPRABART" + j].ToString();
                        rate = System.Convert.ToDecimal(dwData["VRPRABPROZ" + j].ToString())/100;
                        wert = System.Convert.ToDecimal(dwData["VRPRABWERT" + j].ToString())/100;
                        if (term.Trim() != "")
                        {
                            d++;
                            term = Helper.GetDiscountTerm(dsSource, term);
                            exAgent.SetCellValue(posx + 2 +d-1, 19, term);
                            if (rate != 0)
                            {
                                exAgent.SetCellValue(posx + 2 + d - 1, 26, rate+"%");
                            }
                            if (wert != 0)
                            {
                                exAgent.SetCellValue(posx + 2 + d - 1, 33, wert);
                            }
                        }
                        term = dwData["VRPZUART" + j].ToString();
                        rate = System.Convert.ToDecimal(dwData["VRPZUABPROZ" + j].ToString()) / 100;
                        wert = System.Convert.ToDecimal(dwData["VRPZUABWERT" + j].ToString()) / 100;
                        if (term.Trim() != "")
                        {
                            a++;
                            term = Helper.GetAddTerm(dsSource, term);
                            exAgent.SetCellValue(posx + 6 + a - 1, 19, term);
                            if (rate !=0)
                            {
                                exAgent.SetCellValue(posx + 6 + a - 1, 26, rate+"%");
                            }
                            if (wert!= 0)
                            {
                                exAgent.SetCellValue(posx + 6 + a - 1, 33, wert);
                            }
                        }
                    }
                    posx += rpl;
                    if (rown < count)
                    {
                        exAgent.RangeCopy(41, 50, posx, posx+rpl);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                EventDispatcher.Instance.RaiseStatus("完成", StatusEventType.StartProgress);
            }

        }
       

        public void SetTXTValue(string sgb, int x, int y)
        {
            int length = 0;
            string s1 = "";
            string s2 = "";
            try
            {
                s1 = sgb;
                exAgent.SetCellValue(x, y, s1);
                x++;
                return;
                length = sgb.Length;
                if (length <= 50)
                {
                    s1 = sgb;
                    exAgent.SetCellValue(x, y, s1);
                    x++;
                    return;
                }
                s1 = sgb.Substring(0, 50);
                exAgent.SetCellValue(x, y, s1);
                x++;
                s2 = sgb.Substring(50, length - 50);
                exAgent.SetCellValue(x, y, s2);
                x++;
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private string CreateFile(string ordernumer)
        {
            string filename = "";
            string sheetName = "";
            string newfilename = "";
            try
            {
                string pwd = "";
                sheetName = ordernumer;
                newfilename = "发票_" + ordernumer + ".xls";
                exAgent.Open(TemplatePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                List<string> Standards = new List<string>();
                Standards.Add("VR04DOC");
                Standards.Add("VKWZ");
                exAgent.CreateNewSheets(newfilename, Standards, pwd);
                exAgent.Save();
                filename = exAgent.FileName;
                FillInEXCEL(4); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                exAgent.DeleteSheet("VR04DOC");
                exAgent.DeleteSheet("VKWZ");
                exAgent.Save();
                exAgent.Close();
            }
            return filename;
        }
 
        private void ServerShareFile(string ordernumer, string fileType)
        {

            string destfile, tempfile = "";
            string fileabsname = ordernumer + "_" + date;
            try
            {
                filename = destfolder + "\\发票-" + fileabsname + ".xls"; ;
                destfile = filename;
                if (File.Exists(fileabsname + ".xls"))
                {
                    File.Delete(fileabsname + ".xls");
                }
                tempfile = CreateFile(ordernumer);
                if (File.Exists(destfile))
                {
                    File.Delete(destfile);
                }
                File.Move(tempfile, destfile);

                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = true;
            try
            {
                Helper.GetDate(ref date, ref year, ref month, ref day);
                strCompany = "JLT";
                strExcelFile = @"JLT\JLTVR.xls";
                Standard = "VR04DOC";
                TemplatePath = Path.Combine(PathHelper.ApplicationPath, @"Report\JLT\JLTVR.xls");
                destfolder=SetFolder(Root,"发票"); 
                INTEXDBC.OracleDB = OracleDB;
                INTEXDBC.proxy = proxy;
              
                DataRow dwHeader = dsSource.Tables["VRPOS"].Rows[0];

                string ordernumer = Helper.GetSubRollNum(dwHeader["VRPAUFKRS"].ToString()) +"-"+
                    Helper.GetSubRollNum(dwHeader["VRPAUFLFD"].ToString())
                    + "-" + Helper.GetSubRollNum(dwHeader["VRPRENR"].ToString());
                DataRow dwData = INTEXDB.SDDAT.NewRow();
                dwData["SDSA"] = "0255";
                dsSource.Merge(INTEXDBC.QuerySDDAT(dwData, 9999));
                dwData["SDSA"] = "0243";
                dsSource.Merge(INTEXDBC.QuerySDDAT(dwData, 9999));
                dwData["SDSA"] = "0241";
                dsSource.Merge(INTEXDBC.QuerySDDAT(dwData, 9999));
                
                dsSource.Merge(INTEXDBC.QueryHRSTAMM(dwData, 3));
                exAgent.CheckWorkBook(ordernumer);
                ServerShareFile(ordernumer, fileType);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

    }
}
